#!/usr/bin/python3
import os
import time
import shutil
import codecs
import pymysql


# 打开数据库连接
db = pymysql.connect(host="123.206.49.104", user="public",
                     passwd="koo@20161109", db="koo", charset="utf8", port=3306)

# SQL 查询语句
sqlFinish = ("SELECT * FROM t_roomplaybackrecordinfo WHERE status = '%d'" %
       (3))

savefile = "urlFile.txt"

def getUrlListByDb(sql):
    try:
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # 执行SQL语句
        num = cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        #print (results)
        listData = []
        for index in range(num):
            item = results[index]
            localt = time.localtime(item[5])
            stime = time.strftime("%Y-%m-%d %H:%M:%S", localt)
            print("%d roomid:%d, status:%d, title:%s, rebegintime:%s, url:%s" %
                (index, item[0], item[1], item[2], stime, item[9]))
            listData.append(item[9])
    except:
        print("Error: unable to fetch data")
    return listData

def main():
    file = os.path.abspath(savefile)
    urlList = getUrlListByDb(sqlFinish)

    with codecs.open(file, mode='wb',encoding='utf-8') as fp:
        for url in urlList:
            fp.write(url + '\n')
    
    
try:
    main()
finally:
    db.close() # 关闭数据库连接
